package com.jadcon.bedbreakfast.domain;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.springframework.stereotype.Component;

@Component("reservationRepository")
public class ReservationRepositoryInMemoryImpl implements ReservationRepository {

	public ReservationRepositoryInMemoryImpl() throws ParseException {
	}
	
	public List<Reservation> getReservationList() throws SQLException,
			ParseException {

		List<Reservation> reservationList = new ArrayList<Reservation>();

		Connection con = DriverManager.getConnection(
				"jdbc:mysql://localhost:3306/bedandbreakfast", "root", "");
		Statement stmt = con.createStatement();
		ResultSet uprs = stmt
				.executeQuery("SELECT id, guestName, roomNumber, totalNumberOfGuests, checkIn, checkOut, address, city, state, zip, phoneNumber, email FROM reservation");
		System.out.println("Reservations were selected from database");
		System.out.println("");

		while (uprs.next()) {
			int reservationNumber = uprs.getInt("id");
			String guestName = uprs.getString("guestName");
			int roomNumber = uprs.getInt("roomNumber");
			int totalNumberOfGuests = uprs.getInt("totalNumberOfGuests");
			String stringCheckIn = uprs.getString("checkIn");
			String stringCheckOut = uprs.getString("checkOut");
			String address = uprs.getString("address");
			String city = uprs.getString("city");
			String state = uprs.getString("state");
			int zip = uprs.getInt("zip");
			String phone = uprs.getString("phoneNumber");
			String email = uprs.getString("email");

			SimpleDateFormat sdf = new SimpleDateFormat("MM-dd-yyyy");
			Date checkIn = sdf.parse(stringCheckIn);
			Date checkOut = sdf.parse(stringCheckOut);
			

			Reservation reservation = new Reservation(reservationNumber, roomNumber,
					totalNumberOfGuests, checkIn, checkOut, guestName, address,
					city, state, zip, phone, email);

			reservationList.add(reservation);

			System.out.println("Reservations Contained in the DATABASE: "+reservationNumber);


		}

		return reservationList;
	}

	private void insert(Reservation reservation) throws SQLException {
		Connection con = DriverManager.getConnection(
				"jdbc:mysql://localhost:3306/bedandbreakfast", "root", "");
		String sql = "INSERT INTO reservation (roomNumber, totalNumberOfGuests, checkIn, checkOut, guestname, address, city, state, zip, phonenumber, email) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		PreparedStatement pstmt = con
				.prepareStatement(sql);
				
		pstmt.setInt(1, reservation.getRoomNumber());
		pstmt.setInt(2, reservation.getTotalNumberOfGuests());
		pstmt.setString(3, reservation.getCheckInAsString());
		pstmt.setString(4, reservation.getCheckOutAsString());
		pstmt.setString(5, reservation.getGuestName());
		pstmt.setString(6, reservation.getAddress1());
		pstmt.setString(7, reservation.getCity());
		pstmt.setString(8, reservation.getState());
		pstmt.setInt(9, reservation.getZip());
		pstmt.setString(10, reservation.getPhoneNumber());
		pstmt.setString(11, reservation.getEmail());

	pstmt.executeUpdate();

	}

	private void update(Reservation reservation) throws SQLException {
		Connection con = DriverManager.getConnection(
				"jdbc:mysql://localhost:3306/bedandbreakfast", "root", "");
		String sql = "UPDATE reservation SET roomNumber=?, totalNumberOfGuests=?, checkIn=?, checkOut=?, guestname=?, address=?, city=?, state=?, zip=?, phonenumber=?, email=? WHERE id=?";
		PreparedStatement pstmt = con
				.prepareStatement(sql);
				
		pstmt.setInt(1, reservation.getRoomNumber());
		pstmt.setInt(2, reservation.getTotalNumberOfGuests());
		pstmt.setString(3, reservation.getCheckInAsString());  
		pstmt.setString(4, reservation.getCheckOutAsString());
		pstmt.setString(5, reservation.getGuestName());
		pstmt.setString(6, reservation.getAddress1());
		pstmt.setString(7, reservation.getCity());
		pstmt.setString(8, reservation.getState());
		pstmt.setInt(9, reservation.getZip());
		pstmt.setString(10, reservation.getPhoneNumber());
		pstmt.setString(11, reservation.getEmail());

	pstmt.executeUpdate();

		}

	public void save(Reservation reservation) throws SQLException {

		if (reservation.getReservationNumber() == 0) {
			insert(reservation);
		}

		else {
			update(reservation);
		}

	}

	public Reservation findByNumber(int reservationNumber) throws SQLException,
			ParseException {

		Connection con = DriverManager.getConnection(
				"jdbc:mysql://localhost:3306/bedandbreakfast", "root", "");
		String sql = "SELECT id, roomNumber, totalNumberOfGuests, checkIn, checkOut, guestName, address, city, state, zip, phoneNumber, email FROM reservation where id=?";
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setInt(1, reservationNumber);
		ResultSet rs = pstmt.executeQuery();
		System.out.println("Reservation was selected from database");

		while (rs.next()) {
			int  id = rs.getInt("id");
			int roomNumber = rs.getInt("roomNumber");
			int totalNumberOfGuests = rs.getInt("totalNumberOfGuests");
			String stringCheckIn = rs.getString("checkIn");
			String stringCheckOut = rs.getString("checkOut");
			String guestName = rs.getString("guestName");
			String address = rs.getString("address");
			String city = rs.getString("city");
			String state = rs.getString("state");
			int zip = rs.getInt("zip");
			String phone = rs.getString("phoneNumber");
			String email = rs.getString("email");

			SimpleDateFormat sdf = new SimpleDateFormat("MM-dd-yyyy");
			Date checkIn = sdf.parse(stringCheckIn);
			Date checkOut = sdf.parse(stringCheckOut);

			Reservation reservation = new Reservation(id, roomNumber,
					totalNumberOfGuests, checkIn, checkOut, guestName, address,
					city, state, zip, phone, email);

			return reservation;

		}
		return null;
	}

	public List<Reservation> findReservationByRoom(int roomNumber)
			throws SQLException, ParseException {
		final List<Reservation> filteredReservationList = new ArrayList<Reservation>();
		Connection con = DriverManager.getConnection(
				"jdbc:mysql://localhost:3306/bedandbreakfast", "root", "");
		String sql = "SELECT id, roomNumber, totalNumberOfGuests, checkIn, checkOut, guestName, address, city, state, zip, phoneNumber, email FROM reservation where roomNumber=?";
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setInt(1, roomNumber);
		ResultSet rs = pstmt.executeQuery();
		System.out.println("Reservation was selected from database");

		while (rs.next()) {
			int  id = rs.getInt("id");
			int roomNumber1 = rs.getInt("roomNumber");
			int totalNumberOfGuests = rs.getInt("totalNumberOfGuests");
			String stringCheckIn = rs.getString("checkIn");
			String stringCheckOut = rs.getString("checkOut");
			String guestName = rs.getString("guestName");
			String address = rs.getString("address");
			String city = rs.getString("city");
			String state = rs.getString("state");
			int zip = rs.getInt("zip");
			String phone = rs.getString("phoneNumber");
			String email = rs.getString("email");

			SimpleDateFormat sdf = new SimpleDateFormat("MM-dd-yyyy");
			Date checkIn = sdf.parse(stringCheckIn);
			Date checkOut = sdf.parse(stringCheckOut);

			Reservation reservation = new Reservation(id, roomNumber1,
					totalNumberOfGuests, checkIn, checkOut, guestName, address,
					city, state, zip, phone, email);

			filteredReservationList.add(reservation);

		}
		return filteredReservationList;
	}	

	public List<Reservation> findReservationByStartDate(String checkIn)
			throws ParseException, SQLException {
		
	final List<Reservation> filteredReservationList = new ArrayList<Reservation>();
	Connection con = DriverManager.getConnection(
			"jdbc:mysql://localhost:3306/bedandbreakfast", "root", "");
	String sql = "SELECT id, roomNumber, totalNumberOfGuests, checkIn, checkOut, guestName, address, city, state, zip, phoneNumber, email FROM reservation where checkIn=?";
	PreparedStatement pstmt = con.prepareStatement(sql);

	pstmt.setString(1, checkIn);
	ResultSet rs = pstmt.executeQuery();
	System.out.println("Reservation was selected from database");

	while (rs.next()) {
		int  id = rs.getInt("id");
		int roomNumber = rs.getInt("roomNumber");
		int totalNumberOfGuests = rs.getInt("totalNumberOfGuests");
		String stringCheckIn2 = rs.getString("checkIn");
		String stringCheckOut2 = rs.getString("checkOut");
		String guestName = rs.getString("guestName");
		String address = rs.getString("address");
		String city = rs.getString("city");
		String state = rs.getString("state");
		int zip = rs.getInt("zip");
		String phone = rs.getString("phoneNumber");
		String email = rs.getString("email");

		SimpleDateFormat sdf2 = new SimpleDateFormat("MM-dd-yyyy");
		Date checkIn3 = sdf2.parse(stringCheckIn2);
		Date checkOut3 = sdf2.parse(stringCheckOut2);

		Reservation reservation = new Reservation(id, roomNumber,
				totalNumberOfGuests, checkIn3, checkOut3, guestName, address,
				city, state, zip, phone, email);

		filteredReservationList.add(reservation);

	}
	return filteredReservationList;
}	
		
}		
